﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Globalization;

namespace NpoiSample.Helper.CompleteCustomer
{
    public static class WorkbookHelper
    {
        /// <summary>
        /// 创建Workbook
        /// </summary>
        /// <param name="ext"></param>
        /// <param name="fileStream"></param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(ExcelTypeEnum ext, Stream fileStream = null)
        {
            return ext == ExcelTypeEnum.Xls
                ? fileStream == null ? new HSSFWorkbook() : new HSSFWorkbook(fileStream)
                : fileStream == null ? new XSSFWorkbook() : new XSSFWorkbook(fileStream);
        }

        public static ISheet GetSheetOrNull(this IWorkbook workBook, int index)
        {
            try
            {
                return workBook.GetSheetAt(index);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public static ISheet GetSheetOrNull(this IWorkbook workBook, string name)
        {
            try
            {
                return workBook.GetSheet(name);
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 当前行是否是空行
        /// </summary>
        /// <param name="row"></param>
        /// <param name="maxCellIndex"></param>
        /// <returns></returns>
        public static bool IsEmptyOrNull(this IRow row, int? startCellIndex = null, int? maxCellIndex = null)
        {
            if (row == null || row.FirstCellNum < 0) return true;

            var cellStartIndex = startCellIndex ?? row.FirstCellNum;
            var cellEndIndex = maxCellIndex ?? row.LastCellNum;

            for (int cellIndex = cellStartIndex; cellIndex <= cellEndIndex; cellIndex++)
            {
                if (!string.IsNullOrWhiteSpace(row.GetCell(cellIndex).GetValue()))
                    return false;
            }

            return true;
        }

        /// <summary>
        /// 根据Excel列类型获取列的值.
        /// cell不存在返回string.Empty
        /// </summary>
        /// <param name="cell">cell.</param>
        /// <returns>值.</returns>
        public static string GetValue(this ICell cell)
        {
            if (cell == null) return string.Empty;

            var ret = cell.CellType switch
            {
                CellType.Blank => string.Empty,
                CellType.Boolean => cell.BooleanCellValue.ToString(),
                CellType.Error => cell.ErrorCellValue.ToString(),
                CellType.Numeric => DateUtil.IsCellDateFormatted(cell)
                    ? $"{cell.DateCellValue:G}"
                    : cell.NumericCellValue.ToString(CultureInfo.InvariantCulture),
                CellType.String => cell.StringCellValue,
                CellType.Formula => GetFormulaCellValue(cell),
                _ => cell.ToString(),
            };

            return ret?.Trim() ?? string.Empty;

            static string GetFormulaCellValue(ICell cell)
            {
                try
                {
                    var da = cell.Sheet.Workbook.GetType().Name;
                    if (da == "HSSFWorkbook")
                    {
                        var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    else
                    {
                        var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                }
                catch
                {
                    return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                }
            }
        }
    }
}